/**
 * Program   :   JDBCUtils.java
 * Author    :   shadow guo
 * Create    :   2013-6-23 上午10:55:18
 *
 * Copyright 2008 by Tongfu Information Technology Co. Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information
 * of Tongfu Information Technology Co. Ltd. ("Confidential Information").  You
 * shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement
 * you entered into with Tongfu Information Technology Co. Ltd.
 * 
 */
package com.tongfusoft.web.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class JDBCUtils {
	/**
	 * Logger for this class
	 */
	private static final Log logger = LogFactory.getLog(JDBCUtils.class);
	
	/**
	 * 获取数据库连接
	 * @param param
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static  Connection getConn(Map<String, Object> param)
			throws ClassNotFoundException, SQLException {
		Properties prop = new Properties();
		prop.put("user", (String) param.get("DB_USERNAME"));
		prop.put("password", (String) param.get("DB_USERPWD"));
		prop.put("url", (String) param.get("DB_URL"));
		prop.put("remarksReporting", "true");
		
		Class.forName((String) param.get("DB_DRIVER"));
		Connection conn = DriverManager.getConnection(
				(String) param.get("DB_URL"), prop);

		return conn;
	}
	
	/**
	 * 关闭连接
	 * @param conn
	 */
	public static void closeConnection(Connection conn){
		try {
			conn.close();
		} catch (SQLException e) {
			logger.error(e);
		}
	}
	
	/**
	 * 获取指定Schema下的所有表信息，schema可以是%，表示所有
	 * @param conn
	 * @param schema
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String,String>> getAllTable(Connection conn,String schema) throws SQLException{
		List<Map<String,String>> tables = new ArrayList<Map<String,String>>();
		
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs=dbmd.getTables(null, schema, "%", new String[]{"TABLE"});
		ResultSetMetaData rsmd = rs.getMetaData();
		
		while(rs.next()){
			Map<String,String> table = new HashMap<String,String>();
			//TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS
			for(int i=1;i<=rsmd.getColumnCount();i++){
				table.put(rsmd.getColumnName(i),StringUtils.defaultIfEmpty(rs.getString(rsmd.getColumnName(i)),""));
			}
			
			tables.add(table);
		}
		
		try{
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return tables;
	}
	
	/**
	 * 获取表的定义
	 * @param conn
	 * @param schema
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public static TableDefine getTableDefine(Connection conn,String schema,String tableName) throws SQLException{
		TableDefine tableDefine = new TableDefine();
		
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs=dbmd.getTables(null, schema, "%", new String[]{"TABLE"});
		while(rs.next()){
			if(StringUtils.equals(tableName, rs.getString("TABLE_NAME"))){
				tableDefine.setTableName(rs.getString("TABLE_NAME"));
				tableDefine.setTableRemark(rs.getString("REMARKS"));
				tableDefine.setTableSchema(rs.getString("TABLE_SCHEM"));
				break;
			}
		}
		
		try{
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		if(StringUtils.isNotEmpty(tableDefine.getTableName())){
			tableDefine = setColumnDefineByTable(conn,tableDefine);
			tableDefine = setPrimaryKeys(conn,tableDefine);
		}
		
		return tableDefine;
	}
	
	/**
	 * 获取表中字段定义
	 * @param conn
	 * @param tableDefine
	 * @return
	 * @throws SQLException
	 */
	public static TableDefine setColumnDefineByTable(Connection conn,TableDefine tableDefine) throws SQLException{
		
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getColumns(null, tableDefine.getTableSchema(), tableDefine.getTableName(), null);
		
		while(rs.next()){
			ColumnDefine define = new ColumnDefine();
			define.setFieldName(rs.getString("COLUMN_NAME"));
			define.setFieldType(rs.getString("TYPE_NAME"));
			define.setScale(rs.getInt("DECIMAL_DIGITS"));
			define.setRemark(rs.getString("REMARKS"));
			
			tableDefine.addColumn(define);
		}
		
		try{
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return tableDefine;
	}
	
	/**
	 * 获取PK
	 * @param conn
	 * @param tableDefine
	 * @return
	 * @throws SQLException
	 */
	public static TableDefine setPrimaryKeys(Connection conn,TableDefine tableDefine) throws SQLException{
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getPrimaryKeys(null,  tableDefine.getTableSchema(), tableDefine.getTableName());
		
		while(rs.next()){
			tableDefine.addPkColumn(rs.getString("COLUMN_NAME"));
		}
		
		try{
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return tableDefine;
	}
	
	public static TableDefine setUnqieIndex(Connection conn,TableDefine tableDefine) throws SQLException{
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getIndexInfo(null, tableDefine.getTableSchema(), tableDefine.getTableName(), true, true);
//		ResultSetMetaData rsmd = rs.getMetaData();
		while(rs.next()){
			/*TABLE_CAT:
				TABLE_SCHEM:EXAMPLE_USER
				TABLE_NAME:T_DICTIONARY_CLASS
				NON_UNIQUE:0
				INDEX_QUALIFIER:
				INDEX_NAME:INX_DICTIONARYCLASS
				TYPE:1
				ORDINAL_POSITION:1
				COLUMN_NAME:DICTCLASS_CODE
				ASC_OR_DESC:
				CARDINALITY:1
				PAGES:1
				FILTER_CONDITION:*/
			if(StringUtils.isNotEmpty(rs.getString("INDEX_NAME")) && !StringUtils.startsWith(rs.getString("INDEX_NAME"), "PK_")){
				tableDefine.addUniqeIndexColumn(rs.getString("INDEX_NAME"),rs.getString("COLUMN_NAME"));
			}
/*			
			for(int i=1;i<=rsmd.getColumnCount();i++){
				System.out.println(rsmd.getColumnName(i)+ ":" + StringUtils.defaultIfEmpty(rs.getString(rsmd.getColumnName(i)),""));
			}
			System.out.println("============================");
*/		
		}
		
		return tableDefine;
	}
	
}
